package de.eb.webservice.util;

import java.sql.*;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.List;

import com.jcraft.jsch.JSch;
import com.jcraft.jsch.Session;




import de.eb.webservice.domain.Antwort;
import de.eb.webservice.domain.Benutzer;
import de.eb.webservice.domain.Bewertung;
import de.eb.webservice.domain.Frage;



public class Databaseconnect {
	public static String dbHost = "127.0.0.1";
	public static int dbPort = 3306;
	public static String dbName = "npr-db";
	public static String user = "eb10";
	public static String passwd = "eb10";
	public static Connection conn = null; 
	public static final String ssh_user = "eb10";
	public static Session session = null;
	public static final String ssh_server = "iwi-w-vm-eb.hs-karlsruhe.de";
	public static final String ssh_pw = "eb10";
	public static final int ssh_port = 22;
	public static final String rhost = "127.0.0.1";
	public static Benutzer benutzer = null;
	public static Statement stmt = null;
	public static ResultSet rslt = null;
	public static Statement stmt2 = null;
	
	
	
	public static Benutzer findBenutzerbyId(int id){
		
		conn = getConnection();
		try {
			stmt = conn.createStatement();
		} catch (Exception sqle) {
			
			//System.out.println("Statementesrtellung fehlgeschlagen: " + sqle.getMessage()); 
		}
		
		try {
			rslt = stmt.executeQuery("select * from benutzer where idBenutzer ="+id);
		} catch (Exception sqle) {
			
			//System.out.println("ResultSet fehlgeschlagen: " + sqle.getMessage()); 
		} 
		try {
			while (rslt.next()) {
				benutzer = new Benutzer(rslt.getInt(1),rslt.getString(2),rslt.getString(4),rslt.getInt(6),rslt.getString(3),rslt.getInt(5));
				
			}
		} catch (Exception sqle) {
		
			//System.out.println("Abfrage fehlgeschlagen: " + sqle.getMessage()); 
		}
		

			 if (conn != null) {
		            closeConnection();
		            try {
		                stmt.close();
		                stmt = null;
		                rslt.close();
		                rslt = null;
		            } catch (Exception sqle) {
		                // System.out.println(sqle.getMessage());
		            }
		        
			}
		
		return benutzer;
	}
	public static Benutzer findBenutzerByeMail(String eMail){
		Benutzer benutzer = null;
		conn = getConnection();
		try {
			stmt = conn.createStatement();
		} catch (Exception sqle) {
			
			//System.out.println("Statementesrtellung fehlgeschlagen: " + sqle.getMessage()); 
		}
		
		try {
			rslt = stmt.executeQuery("select * from benutzer where eMail = '"+eMail+"'");
		} catch (Exception sqle) {
			
			//return benutzer;
		} 
		try {
			while (rslt.next()) {
				benutzer = new Benutzer(rslt.getInt(1),rslt.getString(2),rslt.getString(4),rslt.getInt(6),rslt.getString(3),rslt.getInt(5));
				
			}
		} catch (Exception sqle) {
		
			//System.out.println("Abfrage fehlgeschlagen: " + sqle.getMessage()); 
		}
		

		 if (conn != null) {
	            closeConnection();
	            try {
	                stmt.close();
	                stmt = null;
	                rslt.close();
	                rslt = null;
	            } catch (Exception sqle) {
	                // System.out.println(sqle.getMessage());
	            }
	        
		}
		
		return benutzer;
	}
	
	public static void createBenutzer(Benutzer bn){
		int rslt = 0;	
		conn = getConnection();
		try {
			stmt = conn.createStatement();
		} catch (Exception sqle) {
			
			//System.out.println("Statementesrtellung fehlgeschlagen: " + sqle.getMessage()); 
		}
		
		try {
			rslt = stmt.executeUpdate("insert into benutzer values(null,'"+bn.getBenutzername()+"','"+bn.getPasswort()+"','"+bn.getEMail()+"',"+bn.getRichtig_beantwortete_Fragen()+","+bn.getFregenEingestellt()+")");
		} catch (Exception sqle) {
			
			//System.out.println("ResultSet fehlgeschlagen: " + sqle.getMessage()); 
		} 
		
		
		if (conn != null) {
	            closeConnection();
	            try {
	                stmt.close();
	                stmt = null;
	              
	            } catch (Exception sqle) {
	                // System.out.println(sqle.getMessage());
	            }
	        
		}
		
		
	}
	
	public static void updateBenutzer(int id, int wert){
		int rslt = 0;	
		conn = getConnection();
		try {
			stmt = conn.createStatement();
		} catch (Exception sqle) {
			
			//System.out.println("Statementesrtellung fehlgeschlagen: " + sqle.getMessage()); 
		}
		
		try {
			rslt = stmt.executeUpdate("update benutzer set richtig_beantwortete_Fragen = richtig_beantwortete_Fragen +  "+wert+" where idBenutzer = "+id) ;
		} catch (Exception sqle) {
			
			//System.out.println("ResultSet fehlgeschlagen: " + sqle.getMessage()); 
		} 
	}
	public static Frage findFragebyId(int id){
		Frage frage = null;
		try{
			conn = getConnection();
		} catch(Exception e){
			
		}
		
		try {
			stmt = conn.createStatement();
		} catch (Exception sqle) {
			
			//System.out.println("Statementesrtellung fehlgeschlagen: " + sqle.getMessage()); 
		} 
			
		
		try {
			rslt = stmt.executeQuery("select * from frage where idFrage ="+id);
		} catch (Exception sqle) {
			
			//System.out.println("ResultSet fehlgeschlagen: " + sqle.getMessage()); 
		} 
		
		try {
			while (rslt.next()) {
				frage = new Frage(rslt.getInt(1),rslt.getTimestamp(6),rslt.getString(3),rslt.getByte(4),rslt.getInt(5),rslt.getInt(2),rslt.getString(7));
				
			}
		} catch (Exception sqle) {
		
			//System.out.println("Abfrage fehlgeschlagen: " + sqle.getMessage()); 
		}
		
		

		 if (conn != null) {
	            closeConnection();
	            try {
	                stmt.close();
	                stmt = null;
	                rslt.close();
	                rslt = null;
	            } catch (Exception sqle) {
	                // System.out.println(sqle.getMessage());
	            }
	        
		}
		
		return frage;
	}
	
	public static int findMaxFrageId() {
        int maxId = 0;
       try {
            conn = getConnection();
        } catch (Exception e) {

        }

        try {
            stmt = conn.createStatement();
        } catch (Exception sqle) {

            // System.out.println("Statementesrtellung fehlgeschlagen: " +
            // sqle.getMessage());
        }

        try {
            rslt = stmt.executeQuery("select max(idFrage) from frage");
        } catch (Exception sqle) {

            // System.out.println("ResultSet fehlgeschlagen: " +
            // sqle.getMessage());
        }

        try {
            while (rslt.next()) {
                maxId = rslt.getInt(1);

            }
        } catch (Exception sqle) {

            // System.out.println("Abfrage fehlgeschlagen: " +
            // sqle.getMessage());
        }


		 if (conn != null) {
	            closeConnection();
	            try {
	                stmt.close();
	                stmt = null;
	                rslt.close();
	                rslt = null;
	            } catch (Exception sqle) {
	                // System.out.println(sqle.getMessage());
	            }
	        
		}
        return maxId;



    }

	

	public static List<Antwort> findAntwortbyFrageId(int id){
		List<Antwort> antwortList = new ArrayList<Antwort>();
		Antwort antwort = null;
		conn = getConnection();
		try {
			stmt = conn.createStatement();
		} catch (Exception sqle) {
			
			//System.out.println("Statementesrtellung fehlgeschlagen: " + sqle.getMessage()); 
		}
		
		try {
			rslt = stmt.executeQuery("select * from antwort where Frage_idFrage ="+id);
		} catch (Exception sqle) {
			
			//System.out.println("ResultSet fehlgeschlagen: " + sqle.getMessage()); 
		} 
		try {
			while (rslt.next()) {
				antwort = new Antwort(rslt.getInt(1),rslt.getString(3),rslt.getByte(4),rslt.getDouble(5),rslt.getDouble(6),rslt.getInt(2));
				antwortList.add(antwort);
			}
		} catch (Exception sqle) {
		
			//System.out.println("Abfrage fehlgeschlagen: " + sqle.getMessage()); 
		}
		

		 if (conn != null) {
	            closeConnection();
	            try {
	                stmt.close();
	                stmt = null;
	                rslt.close();
	                rslt = null;
	            } catch (Exception sqle) {
	                // System.out.println(sqle.getMessage());
	            }
	        
		}
		
		return antwortList;
	}
	
	public static void createAntwort(Antwort antwort)
	{
		int rslt = 0;	
		conn = getConnection();
		try {
			stmt = conn.createStatement();
		} catch (Exception sqle) {
			
			//System.out.println("Statementesrtellung fehlgeschlagen: " + sqle.getMessage()); 
		}
		
		try {
			rslt = stmt.executeUpdate("insert into antwort values (null," + antwort.getFrageId() +",'"+antwort.getAntworttext()+"',"+antwort.getRichtig()+","+antwort.getLongitude()+","+antwort.getLongitude()+")");
		} catch (Exception sqle) {
			
			//System.out.println("ResultSet fehlgeschlagen: " + sqle.getMessage()); 
		}
		 if (conn != null) {
	            closeConnection();
	            try {
	                stmt.close();
	                stmt = null;
	                
	            } catch (Exception sqle) {
	                // System.out.println(sqle.getMessage());
	            }
	        }
	}
	
	
	
	public static List<Bewertung> findBewertungbyFrageId(int id){
		Bewertung bewertung = null;
		List<Bewertung> bewertungList = new ArrayList<Bewertung>();
		conn = getConnection();
		try {
			stmt = conn.createStatement();
		} catch (Exception sqle) {
			
			//System.out.println("Statementesrtellung fehlgeschlagen: " + sqle.getMessage()); 
		}
		
		try {
			rslt = stmt.executeQuery("select idBewertung, Benutzer_idBenutzer, wert, kommentar, Frage_idFrage from bewertung b join frage_has_bewertung f where f.Frage_idFrage ="+id);
		} catch (Exception sqle) {
			
			//System.out.println("ResultSet fehlgeschlagen: " + sqle.getMessage()); 
		} 
		try {
			while (rslt.next()) {
				bewertung = new Bewertung(rslt.getInt(1),rslt.getString(4),rslt.getInt(3),rslt.getInt(2),rslt.getInt(5));
				bewertungList.add(bewertung);
			}
		} catch (Exception sqle) {
		
			//System.out.println("Abfrage fehlgeschlagen: " + sqle.getMessage()); 
		}
		
		 if (conn != null) {
	            closeConnection();
	            try {
	                stmt.close();
	                stmt = null;
	                rslt.close();
	                rslt = null;
	            } catch (Exception sqle) {
	                // System.out.println(sqle.getMessage());
	            }
	        
		}
		
		return bewertungList;
	}
	public static void createBewertung(Bewertung bewertung)
	{
		int rslt = 0;
		int rslt2 = 0;
		conn = getConnection();
		try {
			stmt = conn.createStatement();
			stmt2 = conn.createStatement();
		} catch (Exception sqle) {
			
			//System.out.println("Statementesrtellung fehlgeschlagen: " + sqle.getMessage()); 
		}
		
		try {
			rslt = stmt.executeUpdate("insert into bewertung values (null,"+bewertung.getBenutzerId()+","+bewertung.getWert()+",'"+bewertung.getKommentar()+"')");
			rslt2 = stmt2.executeUpdate("insert into frage_has_bewertung values ("+findMaxBewertungsId()+","+bewertung.getFrageId()+")");
		} catch (Exception sqle) {
			
			//System.out.println("ResultSet fehlgeschlagen: " + sqle.getMessage()); 
		}
		if (conn != null)
		{
			closeConnection();
		    try 
		    { 
		        stmt.close();
		        stmt2.close();
		        stmt=null;
		        stmt2=null;
		       
		        
		        
		    }
		    catch(Exception sqle) 
		    { 
		        //System.out.println(sqle.getMessage()); 
		    }
		}
	}
	public static int findMaxBewertungsId() {
        int maxId = 0;
        try {
            conn = getConnection();
        } catch (Exception e) {

        }

        try {
            stmt = conn.createStatement();
        } catch (Exception sqle) {

            // System.out.println("Statementesrtellung fehlgeschlagen: " +
            // sqle.getMessage());
        }

        try {
            rslt = stmt
                    .executeQuery("select max(idBewertung) from bewertung");
        } catch (Exception sqle) {

            // System.out.println("ResultSet fehlgeschlagen: " +
            // sqle.getMessage());
        }

        try {
            while (rslt.next()) {
                maxId = rslt.getInt(1);

            }
        } catch (Exception sqle) {

            // System.out.println("Abfrage fehlgeschlagen: " +
            // sqle.getMessage());
        }


		 if (conn != null) {
	            closeConnection();
	            try {
	                stmt.close();
	                stmt = null;
	                rslt.close();
	                rslt = null;
	            } catch (Exception sqle) {
	                // System.out.println(sqle.getMessage());
	            }
	        
		}

        return maxId;



    }
	
	public static int sumBewertungByFrageId(int id) {
        int sumWert = 0;
        try {
            conn = getConnection();
        } catch (Exception e) {

        }

        try {
            stmt = conn.createStatement();
        } catch (Exception sqle) {

            // System.out.println("Statementesrtellung fehlgeschlagen: " +
            // sqle.getMessage());
        }

        try {
            rslt = stmt
                    .executeQuery("select sum(wert) from bewertung b join frage_has_bewertung f where f.Frage_idFrage ="+id);
        } catch (Exception sqle) {

            // System.out.println("ResultSet fehlgeschlagen: " +
            // sqle.getMessage());
        }

        try {
            while (rslt.next()) {
            	sumWert = rslt.getInt(1);

            }
        } catch (Exception sqle) {

            // System.out.println("Abfrage fehlgeschlagen: " +
            // sqle.getMessage());
        }


		 if (conn != null) {
	            closeConnection();
	            try {
	                stmt.close();
	                stmt = null;
	                rslt.close();
	                rslt = null;
	            } catch (Exception sqle) {
	                // System.out.println(sqle.getMessage());
	            }
	        
		}

        return sumWert;



    }
	
	public static int anzBewertungByFrageId(int id) {
        int anz = 0;
        try {
            conn = getConnection();
        } catch (Exception e) {

        }

        try {
            stmt = conn.createStatement();
        } catch (Exception sqle) {

            // System.out.println("Statementesrtellung fehlgeschlagen: " +
            // sqle.getMessage());
        }

        try {
            rslt = stmt
                    .executeQuery("select count(wert) from bewertung b join frage_has_bewertung f where f.Frage_idFrage ="+id);
        } catch (Exception sqle) {

            // System.out.println("ResultSet fehlgeschlagen: " +
            // sqle.getMessage());
        }

        try {
            while (rslt.next()) {
            	anz = rslt.getInt(1);

            }
        } catch (Exception sqle) {

            // System.out.println("Abfrage fehlgeschlagen: " +
            // sqle.getMessage());
        }


		 if (conn != null) {
	            closeConnection();
	            try {
	                stmt.close();
	                stmt = null;
	                rslt.close();
	                rslt = null;
	            } catch (Exception sqle) {
	                // System.out.println(sqle.getMessage());
	            }
	        
		}

        return anz;



    }
	
	
	public static void createFrage(Frage frage)
	{
		int rslt = 0;	
		conn = getConnection();
		try {
			stmt = conn.createStatement();
		} catch (Exception sqle) {
			
			//System.out.println("Statementesrtellung fehlgeschlagen: " + sqle.getMessage()); 
		}
		
		try {
			rslt = stmt.executeUpdate("insert into frage values (null,"+1+",'"+frage.getFragestellung()+"',"+frage.getGesperrt()+","+frage.getVersion()+",'"+new Timestamp(System.currentTimeMillis())+"','"+frage.getStadt()+"')");
		} catch (Exception sqle) {
			
			//System.out.println("ResultSet fehlgeschlagen: " + sqle.getMessage()); 
		}
		if (conn != null)
		{
			closeConnection();
		    try 
		    { 
		        stmt.close();
		        stmt = null;
		        
		    }
		    catch(Exception sqle) 
		    { 
		        //System.out.println(sqle.getMessage()); 
		    }
		}
	}
	
	private static Connection getConnection(){
		
		// SSH Tunnel
		   try 
		   {
			   
			   
			   final JSch jsch = new JSch();
			   
			   session = jsch.getSession(ssh_user, ssh_server, ssh_port);
			   final Hashtable<String, String> config = new Hashtable<String, String>();
		       config.put("StrictHostKeyChecking", "no");
		       session.setConfig(config);
		       session.setPassword(ssh_pw);
		       session.connect();
		       int assigned_port = session.setPortForwardingL(dbPort, rhost, dbPort);
		      
		   } 
		   
		   catch (Exception e) 
		   {
		     //e.printStackTrace();
		   }
		try 
		{ 
		    Class.forName("com.mysql.jdbc.Driver"); 
		} 
		catch(Exception cnfe) 
		{ 
		    //System.out.println("Treiber kann nicht geladen werden: "+cnfe.getMessage()); 
		}
		try 
		{ 
			conn = DriverManager.getConnection("jdbc:mysql://" + dbHost + ":" + dbPort + "/" + dbName, user, passwd); 
		} 
		catch(Exception sqle) 
		{ 
		    //System.out.println("Verbindung ist fehlgeschlagen: " + sqle.getMessage()); 
		}
		
		return conn;
		
	}
	
	private static void closeConnection(){
		 try 
		    { 
			 conn.close();
			 conn = null;
			 session.disconnect();
			 session=null;
		       
		    }
		    catch(Exception sqle) 
		    { 
		        //System.out.println(sqle.getMessage()); 
		    }
	}

}
